<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>MySQL入门知识整合 | 后端学习记录</title><meta name="keywords" content="SQL"><meta name="author" content="h0ss"><meta name="copyright" content="h0ss"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="一、SQL基本概念1、SQL（Structured Query Language）​        SQL是一种领域专用编程语言，用于管理关系数据库管理系统（RDBMS），或在关系流数据管理系统（RDSMS）中进行流处理。SQL的范围包括数据的增删改查(CRUD)、数据库模式创建和修改，以及数据访问控制。 2、DB（database）​        数据库是一个以某种有组织方式存储的数据集合，简">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL入门知识整合">
<meta property="og:url" content="https://blog.gpnusz.cn/2021/05/07/MySQL%E7%9F%A5%E8%AF%86%E6%95%B4%E5%90%88/index.html">
<meta property="og:site_name" content="后端学习记录">
<meta property="og:description" content="一、SQL基本概念1、SQL（Structured Query Language）​        SQL是一种领域专用编程语言，用于管理关系数据库管理系统（RDBMS），或在关系流数据管理系统（RDSMS）中进行流处理。SQL的范围包括数据的增删改查(CRUD)、数据库模式创建和修改，以及数据访问控制。 2、DB（database）​        数据库是一个以某种有组织方式存储的数据集合，简">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://tse3-mm.cn.bing.net/th/id/OIP.47fIp5Y4IN4qyJfkBELcqwHaHq?pid=ImgDet&rs=1">
<meta property="article:published_time" content="2021-05-07T05:19:57.000Z">
<meta property="article:modified_time" content="2022-07-28T07:11:30.333Z">
<meta property="article:author" content="h0ss">
<meta property="article:tag" content="SQL">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://tse3-mm.cn.bing.net/th/id/OIP.47fIp5Y4IN4qyJfkBELcqwHaHq?pid=ImgDet&rs=1"><link rel="shortcut icon" href="/img/favicon.png"><link rel="canonical" href="https://blog.gpnusz.cn/2021/05/07/MySQL%E7%9F%A5%E8%AF%86%E6%95%B4%E5%90%88/"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = { 
  root: '/',
  algolia: undefined,
  localSearch: undefined,
  translate: undefined,
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '天',
  date_suffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: undefined,
  lightbox: 'fancybox',
  Snackbar: undefined,
  source: {
    jQuery: 'https://cdn.jsdelivr.net/npm/jquery@latest/dist/jquery.min.js',
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/js/jquery.justifiedGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/css/justifiedGallery.min.css'
    },
    fancybox: {
      js: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.js',
      css: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isanchor: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = { 
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: false,
  postUpdate: '2022-07-28 15:11:30'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    })(window)</script><meta name="generator" content="Hexo 5.4.0"><link rel="stylesheet" href="/css/prism-tomorrow.css" type="text/css"></head><body><div id="web_bg"></div><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="author-avatar"><img class="avatar-img" src="/img/avatar.jpg" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data"><div class="data-item is-center"><div class="data-item-link"><a href="/archives/"><div class="headline">文章</div><div class="length-num">51</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/tags/"><div class="headline">标签</div><div class="length-num">14</div></a></div></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="not-top-img" id="page-header"><nav id="nav"><span id="blog_name"><a id="site-name" href="/">后端学习记录</a></span><div id="menus"><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav></header><main class="layout" id="content-inner"><div id="post"><div id="post-info"><h1 class="post-title">MySQL入门知识整合</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2021-05-07T05:19:57.000Z" title="发表于 2021-05-07 13:19:57">2021-05-07</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2022-07-28T07:11:30.333Z" title="更新于 2022-07-28 15:11:30">2022-07-28</time></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-wordcount"><i class="far fa-file-word fa-fw post-meta-icon"></i><span class="post-meta-label">字数总计:</span><span class="word-count">10.4k</span><span class="post-meta-separator">|</span><i class="far fa-clock fa-fw post-meta-icon"></i><span class="post-meta-label">阅读时长:</span><span>37分钟</span></span></div></div></div><article class="post-content" id="article-container"><h2 id="一、SQL基本概念"><a href="#一、SQL基本概念" class="headerlink" title="一、SQL基本概念"></a>一、SQL基本概念</h2><h3 id="1、SQL（Structured-Query-Language）"><a href="#1、SQL（Structured-Query-Language）" class="headerlink" title="1、SQL（Structured Query Language）"></a>1、SQL（Structured Query Language）</h3><p>​        SQL是一种领域专用编程语言，用于管理关系数据库管理系统（RDBMS），或在关系流数据管理系统（RDSMS）中进行流处理。SQL的范围包括<strong>数据的增删改查(CRUD)<strong>、</strong>数据库模式创建和修改</strong>，以及<strong>数据访问控制</strong>。</p>
<h3 id="2、DB（database）"><a href="#2、DB（database）" class="headerlink" title="2、DB（database）"></a>2、DB（database）</h3><p>​        数据库是一个以某种有组织方式存储的数据集合，简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的资料运行新增、截取、更新、删除等操作，一个数据库由多个表(table)构成。它本质上就是一个<strong>保存有组织数据的容器</strong>。</p>
<h3 id="3、DBMS（database-management-system）"><a href="#3、DBMS（database-management-system）" class="headerlink" title="3、DBMS（database management system）"></a>3、DBMS（database management system）</h3><p>​        数据库管理系统是一种针对<strong>对象数据库</strong>，为<strong>管理数据库</strong>而设计的大型电脑软件管理系统。每种DBMS都会针对SQL进行一定程度的扩展，本文讲的是其中的<strong>MySQL</strong>的语法知识。</p>
<h2 id="二、数据存储特点"><a href="#二、数据存储特点" class="headerlink" title="二、数据存储特点"></a>二、数据存储特点</h2><h3 id="1、表"><a href="#1、表" class="headerlink" title="1、表"></a>1、表</h3><p>​        在关系型数据库中，数据库表是一系列二维数组的集合，可以用来<strong>存储和表示数据对象之间的关系</strong>。每个表的命名都是唯一的，对于MySQL来说指的是在<strong>同一个库中唯一</strong>。存储在同一个表中的数据必须是同一种类型的数据，如现需要存储用户信息和用户订单，那么必须使用两个表来存储相关的数据。</p>
<h3 id="2、行与列"><a href="#2、行与列" class="headerlink" title="2、行与列"></a>2、行与列</h3><p>数据库表由列组成，列表示的是表中的一个字段，可以把所有列组成的集合看成是表格的表头，每一列都有对应的数据类型，它限制了该列存储的数据种类。相对的，表中的一条数据记录称为行。</p>
<h2 id="三、语法规范与常用命令"><a href="#三、语法规范与常用命令" class="headerlink" title="三、语法规范与常用命令"></a>三、语法规范与常用命令</h2><h3 id="1、语法规范"><a href="#1、语法规范" class="headerlink" title="1、语法规范"></a>1、语法规范</h3><p>1）不区分大小写,但建议关键字大写，表名与列名小写<br>2）SQL语句也包括<strong>分号(语句终结符)<strong>，</strong>多条语句必须加上分号</strong>，尽管并不是每个平台都必需在单语句后加分号，但它是作为SQL语法的标准部分定义的<br>3）注释：<br>    单行注释：#注释文字<br>    单行注释：– 注释文字<br>    多行注释：/* 注释文字  */<br>4）对于有歧义的表名/字段名应该使用``进行转义</p>
<h3 id="2、常用命令"><a href="#2、常用命令" class="headerlink" title="2、常用命令"></a>2、常用命令</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 查看当前数据库服务器中所有库</span></span><br><span class="line"><span class="keyword">SHOW</span> DATABASES;</span><br><span class="line"><span class="comment">--2 打开指定的库，下面的所有操作都是对该库进行</span></span><br><span class="line">USE 库名；</span><br><span class="line"><span class="comment">--3 显示库中的所有表</span></span><br><span class="line"><span class="keyword">SHOW</span> TABLES;</span><br><span class="line"><span class="comment">--4 显示指定库中的所有表</span></span><br><span class="line"><span class="keyword">SHOW</span> TABLES <span class="keyword">FROM</span> 库名;</span><br><span class="line"><span class="comment">--5 查看指定表的结构</span></span><br><span class="line"><span class="keyword">DESC</span> 表名;</span><br></pre></td></tr></table></figure>

<h2 id="四、SQL语言分类"><a href="#四、SQL语言分类" class="headerlink" title="四、SQL语言分类"></a>四、SQL语言分类</h2><p>1）<strong>DQL</strong>（Data Query Language）：数据查询语言  如：select<br>2）<strong>DML</strong>(Data Manipulate Language):数据操作语言 如：insert 、update、delete<br>3）<strong>DDL</strong>（Data Define Languge）：数据定义语言 如：create、drop、alter<br>4）<strong>TCL</strong>（Transaction Control Language）：事务控制语言 如：commit、rollback<br>5）<strong>DCL</strong>（Data Control Language）：  数据库控制语言。用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。</p>
<h2 id="五、DQL语言"><a href="#五、DQL语言" class="headerlink" title="五、DQL语言"></a>五、DQL语言</h2><p>使用到的数据库的表结构：</p>
<p><img src="https://www.hualigs.cn/image/608ebea75af2d.jpg"></p>
<p><img src="https://www.hualigs.cn/image/608bf013353ee.jpg"></p>
<p><img src="https://www.hualigs.cn/image/608e7e197e405.jpg"></p>
<h3 id="1、查询-检索【SELECT】"><a href="#1、查询-检索【SELECT】" class="headerlink" title="1、查询/检索【SELECT】"></a>1、查询/检索【SELECT】</h3><p>在MySQL中使用<strong>SELECT语句可以用来检索一个或多个数据列</strong>，标准的SELECT不会对数据库有持久影响。查询允许用户描述所需的数据，将计划、优化以及执行用以产生它选取的结果的物理操作交给数据库管理系统（DBMS）负责。查询包含一系列含有最终结果的字段, 紧跟SELECT关键词。星号（”*”）也可以用来指定查询应当返回查询表所有字段。</p>
<h4 id="1-1-基本查询"><a href="#1-1-基本查询" class="headerlink" title="1.1 基本查询"></a>1.1 基本查询</h4><p>语法：SELECT 查询内容【FROM 表名】;<br>①通过select查询完的结果 是一个虚拟的表格</p>
<p>②要查询的东西可以是常量值、表达式、字段、函数</p>
<p>③可以同时检索多个列，每个列之间用逗号分隔</p>
<p>④可以使用*检索所有字段，但会降低检索性能</p>
<p>⑤使用DISTINCT关键字可以实现去重，该关键字置于列名前</p>
<p>⑥MySQL中可以限制检索结果的条目，limit x,y表示从检索出第x行开始的y行数据</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 查询employees表中的name列信息</span></span><br><span class="line"><span class="keyword">SELECT</span> name <span class="keyword">FROM</span> employees;</span><br><span class="line"><span class="comment">--2 查询employees表中的所有字段信息</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> employees;</span><br><span class="line"><span class="comment">--3 查询employees表中的name、phone字段信息</span></span><br><span class="line"><span class="keyword">SELECT</span> name,phone <span class="keyword">FROM</span> employees;</span><br><span class="line"><span class="comment">--4 DISTINCT关键字实现去重</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> id <span class="keyword">FROM</span> employees;</span><br><span class="line"><span class="comment">--5 limit限制检索结果的条目</span></span><br><span class="line"><span class="keyword">SELECT</span> id <span class="keyword">FROM</span> employees LIMIT <span class="number">3</span>,<span class="number">5</span>;</span><br></pre></td></tr></table></figure>

<h4 id="1-2-排序查询"><a href="#1-2-排序查询" class="headerlink" title="1.2 排序查询"></a>1.2 排序查询</h4><p>语法：SELECT 查询内容 FROM 表 ORDER BY 排序的字段|表达式|函数|别名 【ASC|DESC】</p>
<p>①ORDER BY子句需要位于SELECT语句的最后</p>
<p>②可以使用非检索列进行排序</p>
<p>③可以多个列进行排序，列名之间用逗号隔开</p>
<p>④ASC表示升序排列(默认)，DESC表示降序排列，该标记直接应用到前面的列名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 检索employees表中的id和phone字段 并按照salary进行排序</span></span><br><span class="line"><span class="keyword">SELECT</span> id,phone <span class="keyword">FROM</span> employees <span class="keyword">ORDER</span> <span class="keyword">BY</span> salary;</span><br><span class="line"><span class="comment">--2 检索employees表中的id和phone字段 并按照salary和id进行排序</span></span><br><span class="line"><span class="keyword">SELECT</span> id,phone <span class="keyword">FROM</span> employees <span class="keyword">ORDER</span> <span class="keyword">BY</span> salary,id;</span><br><span class="line"><span class="comment">--3 检索employees表中的id和phone字段 并按照salary降序排序</span></span><br><span class="line"><span class="keyword">SELECT</span> id,phone <span class="keyword">FROM</span> employees <span class="keyword">ORDER</span> <span class="keyword">BY</span> salary <span class="keyword">DESC</span>;</span><br></pre></td></tr></table></figure>

<h4 id="1-3-条件查询"><a href="#1-3-条件查询" class="headerlink" title="1.3 条件查询"></a>1.3 条件查询</h4><p>条件查询指的是在检索所需数据时加入过滤条件，在MySQL中使用的是WHERE子句，该子句位于表名之后。</p>
<p>语法：SELECT 查询内容  FROM  表  WHERE  过滤条件 ;</p>
<p>①过滤条件可以是带有条件运算符(&lt; &gt; = &lt;= &gt;= &lt;&gt;)的一个表达式，如：id&lt;&gt;10，这里的&lt;&gt;为不等于的含义</p>
<p>②如果要匹配范围值，可以使用 BETWEEN x AND y，这代表检索出某字段位于x与y之间的数据；与之类似可以使用IN操作符来指定条件范围</p>
<p>③在MySQL中检测NULL不能直接使用=NULL判断，应该使用IS NULL子句，检测非NULL可以使用IS NOT NULL</p>
<p>④WHERE子句允许多个过滤条件，条件之间使用AND或者OR隔开，需要注意的是AND和OR同时出现时会优先处理AND操作符，可以使用 ( ) 对条件操作顺序进行指定</p>
<p>⑤如果要对数据进行模糊查询可以使用LIKE操作符搭配通配符（%表示匹配出现任意次的任意字符，_表示匹配单个字符，[]表示匹配一个字符集，类似正则），非文本数据类型字段不能模糊查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 查询工资大于1000的员工id</span></span><br><span class="line"><span class="keyword">SELECT</span> id <span class="keyword">FROM</span> employees <span class="keyword">WHERE</span> salary<span class="operator">&gt;</span><span class="number">1000</span>;</span><br><span class="line"><span class="comment">--2 查询工资在1000到3000的员工id</span></span><br><span class="line"><span class="keyword">SELECT</span> id <span class="keyword">FROM</span> employees <span class="keyword">WHERE</span> salary <span class="keyword">BETWEEN</span> <span class="number">1000</span> <span class="keyword">AND</span> <span class="number">3000</span>;</span><br><span class="line"><span class="comment">--3 查询phone不为null的员工的id</span></span><br><span class="line"><span class="keyword">SELECT</span> id <span class="keyword">FROM</span> employees <span class="keyword">WHERE</span> phone <span class="keyword">IS</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>;</span><br><span class="line"><span class="comment">--4 查询phone为null且salary大于1000的员工的id</span></span><br><span class="line"><span class="keyword">SELECT</span> id <span class="keyword">FROM</span> employees <span class="keyword">WHERE</span> phone <span class="keyword">IS</span> <span class="keyword">NULL</span> <span class="keyword">AND</span> salary<span class="operator">&gt;</span><span class="number">1000</span>;</span><br><span class="line"><span class="comment">--5 查询name中结尾包含i字符的员工的id</span></span><br><span class="line"><span class="keyword">SELECT</span> id <span class="keyword">FROM</span> employees <span class="keyword">WHERE</span> name <span class="keyword">LIKE</span> <span class="string">&#x27;%i&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h4 id="1-4-别名查询"><a href="#1-4-别名查询" class="headerlink" title="1.4 别名查询"></a>1.4 别名查询</h4><p>语法：SELECT 查询内容 【AS】 别名  FROM  表 ;</p>
<p>①可以使用别名将计算字段查询出来，这里的计算字段可以是字符串的拼接，也可以是算术表达式的结果</p>
<p>②在MySQL中拼接字段使用的是concat函数，传入需要拼接的字段作为参数即可</p>
<p>③别名查询一般用于替换查询字段/表达式，其中的AS可以省略</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 查询employees表中的员工id并返回为e_id</span></span><br><span class="line"><span class="keyword">SELECT</span> id <span class="keyword">AS</span> e_id <span class="keyword">FROM</span> employees;</span><br><span class="line"><span class="comment">--2 查询employees表中的员工薪资*12</span></span><br><span class="line"><span class="keyword">SELECT</span> salary<span class="operator">*</span><span class="number">12</span> <span class="keyword">AS</span> y_salary <span class="keyword">FROM</span> employees;</span><br><span class="line"><span class="comment">--3 同时查询出员工id和姓名作为一个字段 中间用空格隔开</span></span><br><span class="line"><span class="keyword">SELECT</span> CONCAT(id,<span class="string">&#x27; &#x27;</span>,`name`) id_name <span class="keyword">FROM</span> employees;</span><br></pre></td></tr></table></figure>

<h4 id="1-5-聚集查询"><a href="#1-5-聚集查询" class="headerlink" title="1.5 聚集查询"></a>1.5 聚集查询</h4><p>语法：SELECT 聚集函数  FROM  表 ;</p>
<p>①AVG函数返回某列的平均值，COUNT函数返回某列的行数，MAX函数返回某列的最大值，MIN函数返回某列的最大值，SUM函数返回某列的值总和</p>
<p>②COUNT(*)可以统计对应列的数据行数，不忽略NULL</p>
<p>③对非数值数据使用MAX函数时会返回排序后的最后一行，MIN会返回第一行</p>
<p>④在使用聚集函数时可以结合别名查询，而别名不应该是表中的实际列名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 返回员工salary的平均值与最大值</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">AVG</span>(salary) salary_avg,<span class="built_in">MAX</span>(salary) salary_max</span><br><span class="line"><span class="keyword">FROM</span> employees;</span><br><span class="line"><span class="comment">--2 返回表中员工个数</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">COUNT</span>(<span class="operator">*</span>) sum_employee <span class="keyword">FROM</span> employees;</span><br></pre></td></tr></table></figure>

<p>其他函数：</p>
<blockquote>
<p><strong>1、字符函数</strong><br>concat（x,y,…）                拼接数据x,y,…<br>substr（str,pos,len）        在str字符串中从pos位置开始截取长度为len的子串<br>upper（str）                     将str内容转换成大写<br>lower（str）                    将str内容转换成小写<br>trim（str）                        去除str内容前后的空格<br>ltrim（str）                    去除str内容左边空格<br>rtrim（str）                    去除str内容右边空格<br>replace（str,from_str,to_str）        将str内容中的from_str内容替换为to_str内容<br>lpad（str,len,padstr）        对str使用padstr进行左填充，填充至len长度<br>rpad（str,len,padstr）        对str使用padstr进行右填充，填充至len长度<br>instr（str,substr）        返回str的子串substr第一次出现的索引<br>length（str）        获取str的长度</p>
<p><strong>2、数学函数</strong><br>round（x，y）         对x的小数点后第y位开始进行四舍五入，y省略默认保留整数部分<br>floor（x）                对x向下取整<br>ceil（x）                    对x向上取整<br>mod（a,b）              对a取模b，计算过程：a-a/b*b ，因此如a为负结果负<br>truncate（x，y）    对x从小数点后y位开始截断</p>
<p><strong>3、日期函数</strong><br>now（）                当前系统日期+时间<br>curdate（）            当前系统日期<br>curtime（）            当前系统时间<br>str_to_date（）     将字符转换成日期<br>date_format（）    将日期转换成字符</p>
</blockquote>
<h4 id="1-6-分组查询"><a href="#1-6-分组查询" class="headerlink" title="1.6 分组查询"></a>1.6 分组查询</h4><p>语法：SELECT 查询内容/聚集函数  FROM 表  GROUP BY 分组字段;</p>
<p>①可以按单个字段分组，也可以按多个字段分组，字段之间用逗号隔开，需要注意的是在SELECT中出现的字段必须同时出现在GROUP BY之后</p>
<p>②分组查询也可以进行筛选，如果是分组之前的筛选，操作的是原始表，需要在GROUP BY前使用WHERE筛选；如果是分组之后的筛选，操作的是分组后的结果集，需要在GROUP BY后使用HAVING筛选</p>
<p>③与聚集函数一同查询的字段最好是分组后的字段</p>
<p>④支持对分组后的数据进行排序，ORDER BY须放在语句末尾</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 查询每个部门的人数并按照部门id排序</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">COUNT</span>(<span class="operator">*</span>),department_id <span class="keyword">FROM</span> employees </span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> department_id <span class="keyword">ORDER</span> <span class="keyword">BY</span> department_id;</span><br><span class="line"><span class="comment">--2 查询每个工种的平均工资并排序</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">AVG</span>(salary),job_id <span class="keyword">FROM</span> employees </span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> job_id <span class="keyword">ORDER</span> <span class="keyword">BY</span> salary;</span><br><span class="line"><span class="comment">--3 查询哪个部门的人数大于2</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">COUNT</span>(<span class="operator">*</span>) sum_person,department_id <span class="keyword">FROM</span> employees </span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> department_id <span class="keyword">HAVING</span> sum_person<span class="operator">&gt;</span><span class="number">2</span>;</span><br></pre></td></tr></table></figure>

<h4 id="1-7-连接查询"><a href="#1-7-连接查询" class="headerlink" title="1.7 连接查询"></a>1.7 连接查询</h4><p>在关系型数据库中，用来在一条SELECT语句中关联表的机制称为联结。简单地说，当查询的数据来自不同的表时，就需要使用连接查询。在MySQL中连接查询的标准有<strong>sql92，sql99</strong>，在ANSI SQL中推荐的是使用sql99语法。连接查询种类有<strong>内连接、外连接以及交叉连接</strong>。</p>
<p><strong>1）sql92标准：等值连接/非等值连接/自连接</strong></p>
<p>语法：SELECT 查询内容 FROM 表1,表2…  WHERE 连接条件;</p>
<p>①连接条件不可缺少，否则会出现笛卡尔积(结果集行数为表1行数 * 表2行数 *…)</p>
<p>②可以给表起别名，起别名之后查询时不能使用原始名</p>
<p>③如果在连接查询后需要进行筛选，可在连接条件后用AND连接筛选条件</p>
<p>④自连接表示的是同一张表的连接，这时必须给表起别名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 查询员工姓名对应的部门名</span></span><br><span class="line"><span class="keyword">SELECT</span> `name`,department_name</span><br><span class="line"><span class="keyword">FROM</span> employees,departments</span><br><span class="line"><span class="keyword">WHERE</span> employees.`department_id`<span class="operator">=</span>departments.`department_id`;</span><br><span class="line"><span class="comment">--2 查询电话不为空的员工名和部门名</span></span><br><span class="line"><span class="keyword">SELECT</span> `name`,department_name,phone</span><br><span class="line"><span class="keyword">FROM</span> employees e,departments d</span><br><span class="line"><span class="keyword">WHERE</span> e.`department_id`<span class="operator">=</span>d.`department_id`</span><br><span class="line"><span class="keyword">AND</span> e.`phone` <span class="keyword">IS</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>;</span><br><span class="line"><span class="comment">--3 查询员工名与上级名</span></span><br><span class="line"><span class="keyword">SELECT</span> e.`id` id_employee,e.`name` name_employee,</span><br><span class="line">m.`id` id_manager,m.`name` name_manager</span><br><span class="line"><span class="keyword">FROM</span> employees e,employees m</span><br><span class="line"><span class="keyword">WHERE</span> e.`manager_id`<span class="operator">=</span>m.`id`;</span><br></pre></td></tr></table></figure>

<p><strong>2）sql99标准</strong></p>
<p>语法：SELECT  查询内容  FROM   表1  连接类型  JOIN  表2  IN  连接条件 ;</p>
<p>连接类型：内连接(INNER)，左外连接(LEFT[OUTER])， 右外连接(RIGHT[OUTER]) ，全外连接(FULL[OUTER]) ，交叉连接(CROSS)</p>
<p>①sql99标准中连接查询的好处是在语句中连接条件和筛选条件实现了分离</p>
<p>②内连接与sql92标准除了语法差异外其他并无不同</p>
<p>③对于外连接，假设查找的是a表中有，但是b表中没有的数据，那么如果b表中有与a表匹配的数据则返回，没有则返回null。因此，对于该假设外连接查询结果=内连接结果+a表有但b表无的数据</p>
<p>④对于左外连接，LEFT JOIN 左边是主表；对于右外连接，RIGHT JOIN右边是主表</p>
<p>⑤全连接查询结果=内连接结果+a表中有但b表中没有的+b表中有但a表中没有的</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 查询员工名与对应部门名</span></span><br><span class="line"><span class="keyword">SELECT</span> `name`,department_name</span><br><span class="line"><span class="keyword">FROM</span> employees e</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> departments d</span><br><span class="line"><span class="keyword">ON</span> e.`department_id`<span class="operator">=</span>d.`department_id`;</span><br><span class="line"><span class="comment">--2 查询电话不为空的员工名和部门名</span></span><br><span class="line"><span class="keyword">SELECT</span> `name`,department_name</span><br><span class="line"><span class="keyword">FROM</span> employees e</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> departments d</span><br><span class="line"><span class="keyword">ON</span> e.`department_id`<span class="operator">=</span>d.`department_id`</span><br><span class="line"><span class="keyword">WHERE</span> e.phone <span class="keyword">IS</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>;</span><br><span class="line"><span class="comment">--3 查询员工名与上级名</span></span><br><span class="line"><span class="keyword">SELECT</span> e.`id`,e.`name`,m.`id`,m.`name` </span><br><span class="line"><span class="keyword">FROM</span> employees e</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> employees m</span><br><span class="line"><span class="keyword">ON</span> e.`manager_id`<span class="operator">=</span>m.`id`;</span><br><span class="line"><span class="comment">--4 查询哪个部门没有员工</span></span><br><span class="line"><span class="keyword">SELECT</span> department_name,employees.`id`</span><br><span class="line"><span class="keyword">FROM</span> departments</span><br><span class="line"><span class="keyword">LEFT</span> <span class="keyword">OUTER</span> <span class="keyword">JOIN</span> employees</span><br><span class="line"><span class="keyword">ON</span> employees.`department_id`<span class="operator">=</span>departments.`department_id`</span><br><span class="line"><span class="keyword">WHERE</span> employees.`id` <span class="keyword">IS</span> <span class="keyword">NULL</span></span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> departments.`department_name`;</span><br></pre></td></tr></table></figure>

<h4 id="1-8-子查询"><a href="#1-8-子查询" class="headerlink" title="1.8 子查询"></a>1.8 子查询</h4><p>在一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询，在外面的查询语句，称为主查询或外查询。按结果集来分类有四种子查询，分别是<strong>标量子查询</strong>(结果集一行一列)、<strong>列子查询</strong>(结果集一列多行)、<strong>行子查询</strong>(结果集一行多列)、<strong>表子查询</strong>(结果集一般为多行多列)。</p>
<p>语法：<br>SELECT（标量子查询） FROM（表子查询）<br>WHERE/HAVING （标量子查询/列子查询/行子查询） EXISTS（表子查询）;</p>
<p>①子查询均放在小括号中</p>
<p>②子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧</p>
<p>②子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧</p>
<p>③子查询优先于主查询执行，主查询使用了子查询的执行结果</p>
<p>④单行结果的子查询一般搭配单行操作符使用：&gt; &lt; = &lt;&gt; &gt;= &lt;= ，多行结果的子查询一般搭配多行操作符使用：ANY、ALL、IN、NOT IN</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 查询工资比lily高的用户信息</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> employees</span><br><span class="line"><span class="keyword">WHERE</span> salary<span class="operator">&gt;</span>(</span><br><span class="line">	<span class="keyword">SELECT</span> salary <span class="keyword">FROM</span> employees </span><br><span class="line">    <span class="keyword">WHERE</span> last_name <span class="operator">=</span> &quot;lily&quot;</span><br><span class="line">);</span><br><span class="line"><span class="comment">--2 查询公司工资最少的员工的用户信息</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> employees </span><br><span class="line"><span class="keyword">WHERE</span> salary<span class="operator">=</span>(</span><br><span class="line">	<span class="keyword">SELECT</span> <span class="built_in">MIN</span>(salary)</span><br><span class="line">	<span class="keyword">FROM</span> employees</span><br><span class="line">);</span><br><span class="line"><span class="comment">--3 返回部门位置id是1或者2的对应部门中的所有员工的姓名</span></span><br><span class="line"><span class="keyword">SELECT</span> name <span class="keyword">FROM</span> employees</span><br><span class="line"><span class="keyword">WHERE</span> department_id <span class="keyword">IN</span> (</span><br><span class="line">	<span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> department_id</span><br><span class="line">	<span class="keyword">FROM</span> departments</span><br><span class="line">	<span class="keyword">WHERE</span> location_id</span><br><span class="line">	<span class="keyword">IN</span> (<span class="number">1</span>,<span class="number">2</span>)</span><br><span class="line">);</span><br><span class="line"><span class="comment">--4 查询员工编号最小且工资最高的员工信息</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> employees</span><br><span class="line"><span class="keyword">WHERE</span> (id,salary)<span class="operator">=</span>(</span><br><span class="line">	<span class="keyword">SELECT</span> <span class="built_in">MIN</span>(id),<span class="built_in">MAX</span>(salary)</span><br><span class="line">	<span class="keyword">FROM</span> employees</span><br><span class="line">);</span><br><span class="line"><span class="comment">--5 查询每个部门的员工个数 </span></span><br><span class="line"><span class="keyword">SELECT</span> departments.<span class="operator">*</span>,(</span><br><span class="line">	<span class="keyword">SELECT</span> <span class="built_in">COUNT</span>(<span class="operator">*</span>)</span><br><span class="line">	<span class="keyword">FROM</span> employees e</span><br><span class="line">	<span class="keyword">WHERE</span> e.id<span class="operator">=</span>d.id</span><br><span class="line">) sum_person</span><br><span class="line"><span class="keyword">FROM</span> departments d;</span><br><span class="line"><span class="comment">--6 查询有员工归属的部门名</span></span><br><span class="line"><span class="keyword">SELECT</span> department_name</span><br><span class="line"><span class="keyword">FROM</span> departments d</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">EXISTS</span>(</span><br><span class="line">	<span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> employees e </span><br><span class="line">	<span class="keyword">WHERE</span> e.`department_id`<span class="operator">=</span>d.`department_id`</span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<h4 id="1-9-分页查询"><a href="#1-9-分页查询" class="headerlink" title="1.9 分页查询"></a>1.9 分页查询</h4><p>语法：SELECT  查询内容  FROM  表  LIMIT  起始的索引, 条目数;</p>
<p>①起始条目索引从0开始</p>
<p>②limit子句放在查询语句的最后</p>
<p>③公式：SELECT  *  FROM  表  LIMIT （page-1）*sizePerPage,sizePerPage<br>sizePerPage为每页显示条目数，page为要显示的页数</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 查询前五条员工信息</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> employees</span><br><span class="line">LIMIT <span class="number">0</span>,<span class="number">5</span>;</span><br><span class="line"><span class="comment">--2 查询第11~25条员工信息</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> employees</span><br><span class="line">LIMIT <span class="number">10</span>,<span class="number">15</span>; </span><br><span class="line"><span class="comment">--3 查询电话不为空，并且工资排行前十名的员工信息</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> employees</span><br><span class="line"><span class="keyword">WHERE</span> phone <span class="keyword">IS</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> salary <span class="keyword">DESC</span></span><br><span class="line">LIMIT <span class="number">0</span>,<span class="number">10</span>;</span><br></pre></td></tr></table></figure>

<h4 id="1-10-联合查询"><a href="#1-10-联合查询" class="headerlink" title="1.10 联合查询"></a>1.10 联合查询</h4><p>语法：查询语句1  UNION [ALL]  查询语句2 …</p>
<p>①多条查询语句的查询的列数必须是一致的</p>
<p>②查询后的结果默认是第一条查询语句的表头</p>
<p>③UNION代表去重，UNION ALL代表不去重</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 查询部门编号&gt;100且姓名包含a的员工信息</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> employees <span class="keyword">WHERE</span> department_id<span class="operator">&gt;</span><span class="number">100</span></span><br><span class="line"><span class="keyword">UNION</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> employees <span class="keyword">WHERE</span> `name` <span class="keyword">LIKE</span> <span class="string">&#x27;%a%&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h3 id="2、插入（INSERT）"><a href="#2、插入（INSERT）" class="headerlink" title="2、插入（INSERT）"></a>2、插入（INSERT）</h3><p>语法一：INSERT INTO 表名(字段名，…)  VALUES(值1，…);</p>
<p>语法二：INSERT INTO 表名 SET 字段1=值1,字段2=值2,… ;</p>
<p>①表字段类型和传入的值类型必须一致或兼容，而且一一对应，并且个数也必须一致</p>
<p>②表中允许为空的字段，可用null填充，表中不允许为空的字段，必须插入值</p>
<p>③表名后的字段可以省略，默认插入所有字段，并且顺序应该和表中的存储顺序一致</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 只插入必填字段</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> stuinfo(id,NAME)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">14</span>,<span class="string">&#x27;张三&#x27;</span>);</span><br><span class="line"><span class="comment">--2 按需插入字段</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> stuinfo(NAME,id,phone,sex)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">&#x27;李四&#x27;</span>,<span class="number">15</span>,<span class="string">&#x27;15812345678&#x27;</span>,<span class="string">&#x27;男&#x27;</span>);</span><br><span class="line"><span class="comment">--3 省略字段名，默认全部插入</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> stuinfo</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">16</span>,<span class="string">&#x27;王五&#x27;</span>,<span class="string">&#x27;男&#x27;</span>,<span class="string">&#x27;1999-12-01&#x27;</span>,<span class="string">&#x27;15212345678&#x27;</span>,<span class="keyword">NULL</span>)</span><br><span class="line"><span class="comment">--4 使用语法二插入</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> stuinfo</span><br><span class="line"><span class="keyword">SET</span> id<span class="operator">=</span><span class="number">17</span>,NAME<span class="operator">=</span><span class="string">&#x27;赵六&#x27;</span>,sex<span class="operator">=</span><span class="string">&#x27;男&#x27;</span>,phone<span class="operator">=</span><span class="string">&#x27;13912345678&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h3 id="3、修改（UPDATE）"><a href="#3、修改（UPDATE）" class="headerlink" title="3、修改（UPDATE）"></a>3、修改（UPDATE）</h3><h4 id="3-1-单表修改"><a href="#3-1-单表修改" class="headerlink" title="3.1 单表修改"></a>3.1 单表修改</h4><p>语法：UPDATE 表名 SET 字段1=值1,字段2=值2,… 【筛选条件】；</p>
<h4 id="3-2-多表修改"><a href="#3-2-多表修改" class="headerlink" title="3.2 多表修改"></a>3.2 多表修改</h4><p>语法(sql92)：UPDATE  表1,表2  SET 字段=值,…  WHERE 连接条件  AND 筛选条件;</p>
<p>语法(sql99)：UPDATE 表1 连接类型  JOIN 表2  ON 连接条件  SET 字段=值,…  WHERE 筛选条件;</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 修改stuinfo中nan开头的姓名对应的电话为12300(单表修改)</span></span><br><span class="line">UPDATE stuinfo</span><br><span class="line"><span class="keyword">SET</span> phone<span class="operator">=</span><span class="string">&#x27;12300&#x27;</span></span><br><span class="line"><span class="keyword">WHERE</span> `name` <span class="keyword">LIKE</span> <span class="string">&#x27;nan%&#x27;</span>;</span><br><span class="line"><span class="comment">--2 修改stuinfo表中id为1的姓名为jack，phone为188000(单表修改)</span></span><br><span class="line">UPDATE stuinfo</span><br><span class="line"><span class="keyword">SET</span> `name`<span class="operator">=</span><span class="string">&#x27;jack&#x27;</span>,phone<span class="operator">=</span><span class="string">&#x27;188000&#x27;</span></span><br><span class="line"><span class="keyword">WHERE</span> id<span class="operator">=</span><span class="number">1</span>;</span><br><span class="line"><span class="comment">--3 修改所属部门为“test”的员工的备注信息为“test”</span></span><br><span class="line">UPDATE employees e</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> departments d</span><br><span class="line"><span class="keyword">ON</span> e.`department_id`<span class="operator">=</span>d.`department_id`</span><br><span class="line"><span class="keyword">SET</span> e.remark<span class="operator">=</span><span class="string">&#x27;test&#x27;</span></span><br><span class="line"><span class="keyword">WHERE</span> d.`department_name`<span class="operator">=</span><span class="string">&#x27;test&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h3 id="4、删除（DELETE、TRUNCATE）"><a href="#4、删除（DELETE、TRUNCATE）" class="headerlink" title="4、删除（DELETE、TRUNCATE）"></a>4、删除（DELETE、TRUNCATE）</h3><h4 id="4-1-DELETE"><a href="#4-1-DELETE" class="headerlink" title="4.1 DELETE"></a>4.1 DELETE</h4><p>单表删除：DELETE FROM 表 WHERE 筛选条件；</p>
<p>多表删除（sql92）：DELETE 表名  FROM 表1,表2  WHERE 连接条件 AND 筛选条件；</p>
<p>多表删除（sql99）：DELETE 表名 FROM 表1  连接类型  JOIN 表2 ON 连接条件 WHERE 筛选条件;</p>
<h4 id="4-2-TRUNCATE"><a href="#4-2-TRUNCATE" class="headerlink" title="4.2 TRUNCATE"></a>4.2 TRUNCATE</h4><p>语法：TRUNCATE  TABLE 表名；</p>
<p>①DELETE可以按需求删除数据，TRUNCATE删除的是整个表的数据</p>
<p>②TRUNCATE不能加WHERE条件，而DELETE可以加where条件</p>
<p>③同等情况下，TRUNCATE效率较高</p>
<p>④TRUNCATE删除带自增长的列的表后，如果再插入数据，自增长列数据从1开始，DELETE删除带自增长列的表后，如果再插入数据，自增长列数据从上一次的断点处开始</p>
<p>⑤TRUNCATE删除不能回滚，DELETE删除可以回滚</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 删除手机号以00结尾的用户信息</span></span><br><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> stuinfo <span class="keyword">WHERE</span> phone <span class="keyword">LIKE</span> &quot;%00&quot;;</span><br><span class="line"><span class="comment">--2 删除所属部门为“test”的员工的信息</span></span><br><span class="line"><span class="keyword">DELETE</span> e</span><br><span class="line"><span class="keyword">FROM</span> departments d</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> employees e</span><br><span class="line"><span class="keyword">ON</span> e.`department_id`<span class="operator">=</span>d.`department_id`</span><br><span class="line"><span class="keyword">WHERE</span> d.`department_name`<span class="operator">=</span><span class="string">&#x27;test&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h2 id="六、DML语言"><a href="#六、DML语言" class="headerlink" title="六、DML语言"></a>六、DML语言</h2><h3 id="1、数据类型"><a href="#1、数据类型" class="headerlink" title="1、数据类型"></a>1、数据类型</h3><h4 id="1-1-数值型"><a href="#1-1-数值型" class="headerlink" title="1.1 数值型"></a>1.1 数值型</h4><p><strong>1）整型</strong><br>分类: TINYINT(1字节)  SMALLINT(2字节)  MEDIUMINT(3字节)  INT/INTEGER(4字节)  BIGINT(8字节)</p>
<p>①默认为有符号，如需使用无符号，应标明UNSIGNED</p>
<p>②如果插入值超过范围，会抛出error</p>
<p>③在使用这些类型时可能会出现如：INT(3) 这种形式，实际上括号里面的数只表示显示的最大宽度，而非所占字节数</p>
<p>④搭配ZEROFILL可在长度不够时在左侧填0，在使用ZEROFILL后，对应字段将变成UNSIGNED。如果不设置长度，会给出默认长度 </p>
<p><strong>2）小数</strong><br>2.1）浮点型<br>FLOAT(M,D)  DOUBLE(M,D)</p>
<p>2.2）定点型<br>DEC(M,D)  DECIMAL(M,D)</p>
<p>①M:整数部位+小数部位长度，D：小数部位长度， 如果M超出范围会报错，D超出会四舍五入</p>
<p>②M D可以省略 如果是DECIMAL默认为(10,0)</p>
<p>③FLOAT存储需求为4字节，DOUBLE存储需求为8字节，DEC、DECIMAL存储需求为M+2字节</p>
<p>④定点型精度较浮点型高</p>
<h4 id="1-2-字符型"><a href="#1-2-字符型" class="headerlink" title="1.2 字符型"></a>1.2 字符型</h4><p><strong>1）短文本：</strong><br>CHAR(M):   储存固定长度字符，空间占用大，效率高，M表示长度，可省略，默认为1<br>VARCHAR(M):存储可变长度字符，空间占用小，效率低，M表示长度，不可省略<br><strong>2）长文本：</strong><br>TEXT：保存非二进制字符串<br><strong>3）其他：</strong><br>ENUM：用于保存枚举类型    SET：用于保存集合类型</p>
<p>①不管是哪种形式的字符型，都要包括在括号里(荐小括号)</p>
<p>②VARCHAR和TEXT类型是变长类型，其存储需求取决于列值的实际长度（最终长度为实际长度+1）</p>
<h4 id="1-3-二进制类型"><a href="#1-3-二进制类型" class="headerlink" title="1.3 二进制类型"></a>1.3 二进制类型</h4><p>BIT(M)：位字段类型，大约 (M+7)/8 字节</p>
<p>BINARY(M)：固定长度二进制字符串，M 字节</p>
<p>VARBINARY(M)：可变长度二进制字符串，M+1 字节</p>
<p>TINYBLOB(M)：L+1 字节，L为实际大小</p>
<p>BLOB(M)：L+2 字节，L为实际大小</p>
<p>MEDIUMBLOB(M)：L+3 字节，L为实际大小</p>
<p>LONGBLOB(M)：L+4 字节，L为实际大小</p>
<h4 id="1-4-日期时间类型"><a href="#1-4-日期时间类型" class="headerlink" title="1.4 日期时间类型"></a>1.4 日期时间类型</h4><p>TIME：时间，表示形式为：HH:MM:SS</p>
<p>DATE：日期，表示形式为：YYYY-MM-DD</p>
<p>DATETIME： 日期+时间，表示形式为：YYYY-MM-DD HH:MM:SS</p>
<p>TIMESTAMP： 日期+时间，也称为时间戳，表示形式为：YYYY-MM-DD HH:MM:SS</p>
<p>YEAR： 年份，表示形式为：YYYY</p>
<p>①DATETIME为8字节，表示范围为1000-9999，不受时区影响</p>
<p>②TIMESTAMP为4字节，表示范围为1970-2038，受时区影响</p>
<p>③DATETIME 在存储日期数据时，按实际输入的格式存储，与时区无关；而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。</p>
<h3 id="2、表的管理"><a href="#2、表的管理" class="headerlink" title="2、表的管理"></a>2、表的管理</h3><h4 id="2-1-新增表"><a href="#2-1-新增表" class="headerlink" title="2.1 新增表"></a>2.1 新增表</h4><p>语法：CREATE TABLE 表名( 列名 列类型 [约束],  列名 列类型 [约束], … );</p>
<p>可以在列类型后加上DEFAULT关键字指定字段的默认值</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 为方便阅读这里只显示基本语法(不含约束)</span></span><br><span class="line"><span class="comment">-- 创建书籍表</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `books`(</span><br><span class="line">	id <span class="type">INT</span>,</span><br><span class="line">	bname <span class="type">VARCHAR</span>(<span class="number">20</span>),</span><br><span class="line">	price <span class="keyword">DOUBLE</span>,</span><br><span class="line">	authorId <span class="type">INT</span>,</span><br><span class="line">	publicDate DATETIME</span><br><span class="line">);</span><br><span class="line"><span class="comment">-- 创建authors表</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `authors`(</span><br><span class="line">	id <span class="type">INT</span>,</span><br><span class="line">	au_name <span class="type">VARCHAR</span>(<span class="number">20</span>),</span><br><span class="line">	nation <span class="type">VARCHAR</span>(<span class="number">20</span>)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<h4 id="2-2-修改表"><a href="#2-2-修改表" class="headerlink" title="2.2 修改表"></a>2.2 修改表</h4><p>语法： ALTER TABLE 表名 CHANGE|MODIFY|ADD|DROP COLUMN 列名 [列类型 约束]</p>
<p>①修改列名，使用的是CHANGE</p>
<p>②修改列的类型或约束，使用的是MODIFY</p>
<p>③添加/删除列，使用的是ADD/DROP</p>
<p>④修改表名，使用的是RENAME</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 修改books表的publicDate字段为pubDate，类型为DATETIME</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> books CHANGE <span class="keyword">COLUMN</span> publicDate pubDate DATETIME;</span><br><span class="line"><span class="comment">--2 修改books表中pubDate列的类型为TIMESTAMP</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> books MODIFY <span class="keyword">COLUMN</span> pubDate <span class="type">TIMESTAMP</span>;</span><br><span class="line"><span class="comment">--3 向authors表添加annual列，类型为DOUBLE</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> `authors` <span class="keyword">ADD</span> <span class="keyword">COLUMN</span> annual <span class="keyword">DOUBLE</span>;</span><br><span class="line"><span class="comment">--4 删除authors表中的annual字段</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> `authors` <span class="keyword">DROP</span> <span class="keyword">COLUMN</span> annual;</span><br><span class="line"><span class="comment">--5 将authors表更名为books_authors</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> `authors` RENAME <span class="keyword">TO</span> books_authors;</span><br></pre></td></tr></table></figure>

<h4 id="2-3-删除表"><a href="#2-3-删除表" class="headerlink" title="2.3 删除表"></a>2.3 删除表</h4><p>语法：DROP TABLE [IF EXISTS] 表名;</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 加上IF EXISTS，如果表存在才删除(避免报错)</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> IF <span class="keyword">EXISTS</span> books;</span><br></pre></td></tr></table></figure>

<h4 id="2-4-复制表"><a href="#2-4-复制表" class="headerlink" title="2.4 复制表"></a>2.4 复制表</h4><p>1）仅复制表结构</p>
<p>语法：CREATE TABLE 复制到的表 LIKE 被复制的表;</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 将authors表的结构复制到copy_authors</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `copy_authors` <span class="keyword">LIKE</span> `authors`;</span><br></pre></td></tr></table></figure>

<p>2）复制表结构+全部数据</p>
<p>语法：CREATE TABLE  复制到的表  SELECT * FROM 被复制的表;</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 将authors表的结构与全部数据复制到copy_authors2</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `copy_authors2` <span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> `authors`;</span><br></pre></td></tr></table></figure>

<p>3）复制结构+部分数据</p>
<p>语法：CREATE TABLE  复制到的表  SELECT * FROM 被复制的表 WHERE 筛选条件;</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 将authors表的结构与id为1的数据复制到copy_authors3</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `copy_authors3` <span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> `authors` <span class="keyword">WHERE</span> id<span class="operator">=</span><span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<p>4）复制部分结构 不复制数据</p>
<p>语法：CREATE TABLE 复制到的表  SELECT 列名 FROM 被复制的表  WHERE FALSE;</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 将authors表的id、nation列结构复制到copy_authors4</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `copy_authors4` <span class="keyword">SELECT</span> id,nation <span class="keyword">FROM</span> `authors` <span class="keyword">WHERE</span> <span class="literal">FALSE</span>;</span><br></pre></td></tr></table></figure>

<h3 id="3、约束"><a href="#3、约束" class="headerlink" title="3、约束"></a>3、约束</h3><h4 id="3-1-概述"><a href="#3-1-概述" class="headerlink" title="3.1 概述"></a>3.1 概述</h4><p>约束用于限制表中的数据，保证数据的准确性和可靠性。</p>
<h4 id="3-2-分类"><a href="#3-2-分类" class="headerlink" title="3.2 分类"></a>3.2 分类</h4><p>MySQL中的约束共有五种</p>
<p>①NOT NULL 非空 – 保证字段不为空<br>②DEFAULTS 默认 – 保证字段有默认值<br>③PRIMARY KEY 主键 – 保证字段的唯一性，并且非空<br>④UNIQUE 唯一 – 保证字段的唯一性，可以为空<br>⑤FOREIGN KEY 外键 – 保证该字段的值来自主表的关联列的值，外键在从表中添加</p>
<p>按照他们的定义位置又可以分为列级约束和表级约束，列级约束支持除外键之外的约束定义，表级约束支持除了非空与默认，其他都支持。</p>
<h4 id="3-3-添加约束"><a href="#3-3-添加约束" class="headerlink" title="3.3 添加约束"></a>3.3 添加约束</h4><p><strong>1） 创建表时添加列级约束</strong></p>
<p>语法：CREATE TABLE 表名( 列名1 列类型1 约束1,  列名2 列类型2 约束2, … );</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> stuinfo2(</span><br><span class="line">	id <span class="type">INT</span> <span class="keyword">PRIMARY</span> KEY,<span class="comment">--主键</span></span><br><span class="line">	stuName <span class="type">VARCHAR</span>(<span class="number">20</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>, <span class="comment">--非空</span></span><br><span class="line">	seat <span class="type">INT</span> <span class="keyword">UNIQUE</span>, <span class="comment">--唯一</span></span><br><span class="line">	age <span class="type">INT</span> <span class="keyword">DEFAULT</span> <span class="number">18</span>, <span class="comment">--默认</span></span><br><span class="line">    gender <span class="type">CHAR</span>(<span class="number">1</span>),</span><br><span class="line">    majorId <span class="type">INT</span></span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<p><strong>2） 创建表时添加表级约束</strong></p>
<p>语法：[constraint 约束名] 约束类型(字段名)</p>
<p>外键定义语法为：CONSTRAINT 约束名 FOREIGN KEY(从表字段名) REFERENCES 主表(字段名)</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> stuinfo3(</span><br><span class="line">	id <span class="type">INT</span>,</span><br><span class="line">	stuName <span class="type">VARCHAR</span>(<span class="number">20</span>),</span><br><span class="line">	gender <span class="type">CHAR</span>(<span class="number">1</span>),</span><br><span class="line">	seat <span class="type">INT</span>,</span><br><span class="line">	age <span class="type">INT</span>,</span><br><span class="line">	majorId <span class="type">INT</span>,</span><br><span class="line">	<span class="keyword">CONSTRAINT</span> pk <span class="keyword">PRIMARY</span> KEY(id),<span class="comment">--主键</span></span><br><span class="line">	<span class="keyword">CONSTRAINT</span> uq <span class="keyword">UNIQUE</span>(seat),<span class="comment">--唯一</span></span><br><span class="line">	<span class="keyword">CONSTRAINT</span> fk <span class="keyword">FOREIGN</span> KEY(majorId) <span class="keyword">REFERENCES</span> major(id)<span class="comment">--外键 </span></span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<p><strong>3） 修改表时添加列级约束</strong></p>
<p>语法：ALTER TABLE 表名 MODIFY COLUMN 字段 字段类型 约束;</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 假设已存在空表stuinfo4（结构与stuinfo3一致但没有约束）</span></span><br><span class="line"><span class="comment">--1 添加非空约束</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> stuinfo5 MODIFY <span class="keyword">COLUMN</span> stuName <span class="type">VARCHAR</span>(<span class="number">20</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>;</span><br><span class="line"><span class="comment">--2 添加默认约束</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> stuinfo5 MODIFY <span class="keyword">COLUMN</span> age <span class="type">INT</span> <span class="keyword">DEFAULT</span> <span class="number">18</span>;</span><br><span class="line"><span class="comment">--3 添加主键约束</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> stuinfo5 MODIFY <span class="keyword">COLUMN</span> id <span class="type">INT</span> <span class="keyword">PRIMARY</span> KEY;</span><br><span class="line"><span class="comment">--4 添加唯一键</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> stuinfo5 MODIFY <span class="keyword">COLUMN</span> seat <span class="type">INT</span> <span class="keyword">UNIQUE</span>;</span><br></pre></td></tr></table></figure>

<p><strong>4） 修改表时添加表级约束</strong></p>
<p>语法：ALTER TABLE 表名 ADD 【constraint 约束名】 约束类型(字段名) 【外键引用…】</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 添加主键约束</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> stuinfo5 <span class="keyword">ADD</span> <span class="keyword">PRIMARY</span> KEY(id);</span><br><span class="line"><span class="comment">--2 添加唯一键</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> stuinfo5 <span class="keyword">ADD</span> <span class="keyword">UNIQUE</span>(seat);</span><br><span class="line"><span class="comment">--3 添加外键</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> stuinfo5 <span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> fk_stuinfo5_major <span class="keyword">FOREIGN</span> KEY(majorId) <span class="keyword">REFERENCES</span> major(id);</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> stuinfo5 <span class="keyword">ADD</span> <span class="keyword">FOREIGN</span> KEY(majorId) <span class="keyword">REFERENCES</span> major(id);</span><br></pre></td></tr></table></figure>

<h4 id="3-4-删除约束"><a href="#3-4-删除约束" class="headerlink" title="3.4 删除约束"></a>3.4 删除约束</h4><p>语法1：ALTER TABLE 表名 MODIFY COLUMN 字段 字段类型 约束的反值;</p>
<p>语法2：ALTER TABLE 表名 DROP 约束类型 约束名;</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 删除非空约束</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> stuinfo5 MODIFY <span class="keyword">COLUMN</span> stuName <span class="type">VARCHAR</span>(<span class="number">20</span>) <span class="keyword">NULL</span>;</span><br><span class="line"><span class="comment">--2 删除默认约束</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> stuinfo5 MODIFY <span class="keyword">COLUMN</span> age <span class="type">INT</span> ;</span><br><span class="line"><span class="comment">--3 删除主键</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> stuinfo5 <span class="keyword">DROP</span> <span class="keyword">PRIMARY</span> KEY;</span><br><span class="line"><span class="comment">--4 删除唯一</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> stuinfo5 <span class="keyword">DROP</span> INDEX seat;</span><br><span class="line"><span class="comment">--5 删除外键</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> stuinfo5 <span class="keyword">DROP</span> <span class="keyword">FOREIGN</span> KEY fk_stuinfo5_major;</span><br></pre></td></tr></table></figure>

<h2 id="七、DDL语言"><a href="#七、DDL语言" class="headerlink" title="七、DDL语言"></a>七、DDL语言</h2><h3 id="1、库的管理"><a href="#1、库的管理" class="headerlink" title="1、库的管理"></a>1、库的管理</h3><h4 id="1-1-新增库"><a href="#1-1-新增库" class="headerlink" title="1.1 新增库"></a>1.1 新增库</h4><p>语法：CREATE DATABASE  [IF NOT EXISTS]  库名;</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 加上IF NOT EXISTS，如果数据库不存在才创建(避免报错)</span></span><br><span class="line"><span class="keyword">CREATE</span> DATABASE IF <span class="keyword">NOT</span> <span class="keyword">EXISTS</span> dbtest;</span><br></pre></td></tr></table></figure>

<h4 id="1-2-删除库"><a href="#1-2-删除库" class="headerlink" title="1.2  删除库"></a>1.2  删除库</h4><p>语法：DROP DATABASE [IF EXISTS] 库名;</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 加上IF EXISTS，如果数据库存在才删除(避免报错)</span></span><br><span class="line"><span class="keyword">DROP</span> DATABASE IF <span class="keyword">EXISTS</span> dbtest;</span><br></pre></td></tr></table></figure>

<h2 id="八、TCL语言"><a href="#八、TCL语言" class="headerlink" title="八、TCL语言"></a>八、TCL语言</h2><h3 id="1、事务"><a href="#1、事务" class="headerlink" title="1、事务"></a>1、事务</h3><h4 id="1-1-事务概念"><a href="#1-1-事务概念" class="headerlink" title="1.1 事务概念"></a>1.1 事务概念</h4><p><strong>事务</strong>：一组逻辑操作单元，使数据从一种状态变换到另一种状态。需要注意的是在MySQL的存储引擎中，只有InnoDB支持事务。</p>
<p><strong>事务处理（事务操作）：</strong>保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被**提交(commit)<strong>，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务</strong>回滚(rollback)**到最初状态。 </p>
<h4 id="1-2-事务特性-ACID"><a href="#1-2-事务特性-ACID" class="headerlink" title="1.2 事务特性(ACID)"></a>1.2 事务特性(ACID)</h4><p><strong>1）原子性（Atomicity）</strong><br>原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。 </p>
<p><strong>2）一致性（Consistency）</strong><br>事务必须使数据库从一个一致性状态变换到另外一个一致性状态。</p>
<p><strong>3）隔离性（Isolation）</strong><br>事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。</p>
<p><strong>4）持久性（Durability）</strong><br>持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。</p>
<h4 id="1-3-事务创建"><a href="#1-3-事务创建" class="headerlink" title="1.3 事务创建"></a>1.3 事务创建</h4><p><strong>1）隐式事务</strong></p>
<p>隐式事务没有明显的开始结束标志，如 INSERT、DELETE、UPDATE等。</p>
<p><strong>2）显式事务</strong></p>
<p>显式事务有明显的开始结束标志，在进行事务创建前要确保自动提交功能为禁用。</p>
<p>步骤①：关闭自动提交功能 </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SET</span> autocommit<span class="operator">=</span><span class="number">0</span>;</span><br></pre></td></tr></table></figure>

<p>步骤②：编写事务中的SQL语句(SELECT、INSERT、UPDATE、DELETE …)</p>
<p>步骤③：结束事务，使用COMMIT提交事务；使用ROLLBACK回滚事务；搭配使用ROLLBACK与SAVEPOINT可以回滚到保存点</p>
<p>需要注意的是如果使用TRUNCATE删除表，那么即使用ROLLBACK回滚，数据仍会被删除。也就是说对于CREATE、DROP等操作，即使回滚也无法撤销。</p>
<p><strong>3）实例</strong></p>
<p>下面用一个常见的转账场景来说明事务如何创建以及有何作用</p>
<p>假设有这么一个用户信息表：有id,姓名,存款三个字段</p>
<p><img src="https://www.hualigs.cn/image/609249ba1e1ff.jpg"></p>
<p>现在张三给李四转账100块钱，正常来说张三账户出账和李四账户进账应该是一个整体，不可以独立完成，因此这个过程在数据库中就可以称之为事务。实际在开发中通过捕获异常或者添加某些条件，可以设置合适的提交时机以及回滚前提。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 模拟事务正常执行提交时，此时事务生效，数据被持久化到数据库中</span></span><br><span class="line"><span class="keyword">SET</span> autocommit<span class="operator">=</span><span class="number">0</span>;</span><br><span class="line">UPDATE money_test <span class="keyword">SET</span> deposit<span class="operator">=</span><span class="number">1100</span> <span class="keyword">WHERE</span> id<span class="operator">=</span><span class="number">1</span>;</span><br><span class="line">UPDATE money_test <span class="keyword">SET</span> deposit<span class="operator">=</span><span class="number">900</span> <span class="keyword">WHERE</span> id<span class="operator">=</span><span class="number">2</span>;</span><br><span class="line"><span class="keyword">COMMIT</span>;</span><br><span class="line"><span class="comment">--2 模拟事务出现异常回滚时，此时事务回滚，数据保持不变</span></span><br><span class="line"><span class="keyword">SET</span> autocommit<span class="operator">=</span><span class="number">0</span>;</span><br><span class="line">UPDATE money_test <span class="keyword">SET</span> deposit<span class="operator">=</span><span class="number">1100</span> <span class="keyword">WHERE</span> id<span class="operator">=</span><span class="number">1</span>;</span><br><span class="line">UPDATE money_test <span class="keyword">SET</span> deposit<span class="operator">=</span><span class="number">900</span> <span class="keyword">WHERE</span> id<span class="operator">=</span><span class="number">2</span>;</span><br><span class="line"><span class="keyword">ROLLBACK</span>;</span><br></pre></td></tr></table></figure>

<h3 id="2、隔离级别"><a href="#2、隔离级别" class="headerlink" title="2、隔离级别"></a>2、隔离级别</h3><h4 id="2-1-事务并发问题"><a href="#2-1-事务并发问题" class="headerlink" title="2.1 事务并发问题"></a>2.1 事务并发问题</h4><p>对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:</p>
<p><strong>1）脏读</strong>: 对于两个事务 T1、T2，T1 读取了已经被 T2 更新但还<strong>没有被提交</strong>的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的。</p>
<p><strong>2）不可重复读</strong>: 对于两个事务T1、T2， T1 读取了一个字段, 然后 T2 <strong>更新</strong>了该字段。之后, T1再次读取同一个字段, 值就不同了。</p>
<p><strong>3）幻读</strong>: 对于两个事务T1、T2，T1 从一个表中读取了一个字段, 然后 T2 在该表中<strong>插入</strong>了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行。</p>
<h4 id="2-2-事务隔离"><a href="#2-2-事务隔离" class="headerlink" title="2.2 事务隔离"></a>2.2 事务隔离</h4><p>数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。一个事务与其他事务隔离的程度称为<strong>隔离级别</strong>。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, <strong>隔离级别越高, 数据一致性就越好, 但并发性越弱。</strong></p>
<h4 id="2-3-隔离级别"><a href="#2-3-隔离级别" class="headerlink" title="2.3 隔离级别"></a>2.3 隔离级别</h4><p><img src="https://www.hualigs.cn/image/6092505c19a92.jpg" alt="图源：尚硅谷"></p>
<p>MySQL支持 4 种事务隔离级别，默认的事务隔离级别为: <strong>REPEATABLE READ。</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 查看当前的隔离级别: </span></span><br><span class="line"><span class="keyword">SELECT</span> @<span class="variable">@tx</span>_isolation;</span><br><span class="line"><span class="comment">--2 设置当前 mySQL 连接的隔离级别为READ COMMITTED:  </span></span><br><span class="line"><span class="keyword">SET</span> TRANSACTION ISOLATION LEVEL READ COMMITTED;</span><br><span class="line"><span class="comment">--3 设置数据库系统的全局的隔离级别为READ COMMITTED:</span></span><br><span class="line"><span class="keyword">SET</span> <span class="keyword">GLOBAL</span> TRANSACTION ISOLATION LEVEL READ COMMITTED;</span><br></pre></td></tr></table></figure>

<h2 id="九、视图"><a href="#九、视图" class="headerlink" title="九、视图"></a>九、视图</h2><p>视图是虚拟的表，和普通表一样使用，其中的数据来自于自定义语句中查询的表，并且是在使用视图时动态生成的，只保留SQL逻辑。视图中的数据是依赖于真实表中的数据的，一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。一般用于替换复用程度高/结构复杂的SQL语句，有时也为了保护数据提高安全性。</p>
<h3 id="1、创建视图"><a href="#1、创建视图" class="headerlink" title="1、创建视图"></a>1、创建视图</h3><p>创建语法：CREATE VIEW 视图名  AS  查询语句;</p>
<p>假设对于本文的employees表：现在只想把其中的name和phone开放给其他人查看，那么要新建一个表吗？有新数据了怎么办？或许建立视图可以很好解决这个问题。我们可以建立一个仅包含name和phone列数据的视图，由于它只是对原始数据表的一个展示，因此也不用担心数据同步的问题。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 创建视图名为employees_view</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">VIEW</span> employees_view </span><br><span class="line"><span class="keyword">AS</span> </span><br><span class="line"><span class="keyword">SELECT</span> `name`,`phone` <span class="keyword">FROM</span> employees;</span><br></pre></td></tr></table></figure>

<h3 id="2、查看视图"><a href="#2、查看视图" class="headerlink" title="2、查看视图"></a>2、查看视图</h3><p>语法1：DESC 视图名;     【查看字段信息】</p>
<p>语法2：SHOW CREATE VIEW 视图名;    【查看视图的详细定义】</p>
<h3 id="3、修改视图"><a href="#3、修改视图" class="headerlink" title="3、修改视图"></a>3、修改视图</h3><p>语法1：CREATE OR REPLACE VIEW 视图名  AS  查询语句;</p>
<p>语法2：ALTER VIEW 视图名  AS  查询语句;</p>
<h3 id="4、删除视图"><a href="#4、删除视图" class="headerlink" title="4、删除视图"></a>4、删除视图</h3><p>语法：DROP VIEW 视图名1,视图名2, … ;</p>
<h3 id="5、视图更新"><a href="#5、视图更新" class="headerlink" title="5、视图更新"></a>5、视图更新</h3><p>如果通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据，因此要满足基本表的数据定义。对于可更新的视图，视图中的行和基本表的行之间必须具有一对一的关系，具有以下特点的视图不能更新：<br>①包含关键字： GROUP BY，DISTINCT，HAVING ，UNION，UNION ALL。<br>②视图是常量视图<br>③SELECT中包含子查询<br>④数据源是一个不能更改的视图，查询语句中的FROM后面是一个不能更改的视图</p>
<h2 id="十、存储过程与函数"><a href="#十、存储过程与函数" class="headerlink" title="十、存储过程与函数"></a>十、存储过程与函数</h2><h3 id="1、变量"><a href="#1、变量" class="headerlink" title="1、变量"></a>1、变量</h3><p>MySQL中的变量可分为系统变量和自定义变量。</p>
<p>1）系统变量又包括会话变量（session）与全局变量（global），需要注意的是服务器每次重启都会为全局变量赋初值，也就是说修改的全局变量重启之后将会恢复原来的值（即不能跨重启）。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 查看所有会话变量</span></span><br><span class="line"><span class="keyword">SHOW</span> SESSION VARIABLES; </span><br><span class="line"><span class="comment">--2 查看所有全局变量</span></span><br><span class="line"><span class="keyword">SHOW</span> <span class="keyword">GLOBAL</span> VARIABLES; </span><br><span class="line"><span class="comment">--3 查看满足条件的变量</span></span><br><span class="line"><span class="keyword">SHOW</span> SESSION VARIABLES <span class="keyword">LIKE</span> <span class="string">&#x27;%char%&#x27;</span>;</span><br><span class="line"><span class="keyword">SHOW</span> <span class="keyword">GLOBAL</span> VARIABLES <span class="keyword">LIKE</span> <span class="string">&#x27;%char%&#x27;</span>;</span><br><span class="line"><span class="comment">--4 查看指定的变量</span></span><br><span class="line"><span class="keyword">SELECT</span> @<span class="variable">@global</span>.变量名</span><br><span class="line"><span class="keyword">SELECT</span> @<span class="variable">@session</span>.变量名	或	<span class="keyword">SELECT</span> @@变量名</span><br><span class="line"><span class="comment">--5 为全局变量赋值</span></span><br><span class="line">方式①： <span class="keyword">set</span> <span class="keyword">global</span> 变量名<span class="operator">=</span>值</span><br><span class="line">方式②： <span class="keyword">set</span> @<span class="variable">@global</span>.变量名<span class="operator">=</span>值</span><br><span class="line"><span class="comment">--6 为会话变量赋值</span></span><br><span class="line">方式①： <span class="keyword">set</span> session 变量名<span class="operator">=</span>值</span><br><span class="line">方式②： <span class="keyword">set</span> @<span class="variable">@session</span>.变量名<span class="operator">=</span>值</span><br><span class="line">方式③： <span class="keyword">set</span> @@变量名<span class="operator">=</span>值</span><br></pre></td></tr></table></figure>

<p>2）自定义变量又分为用户变量与局部变量，用户变量在设置的当前连接/会话有效，而局部变量仅在BEGIN END中有效，且需要定义在首部。自定义变量需要先声明、赋值再使用。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 用户变量</span></span><br><span class="line"><span class="comment">--1 声明并初始化</span></span><br><span class="line">方式①： <span class="keyword">SET</span> @变量名<span class="operator">=</span>值;</span><br><span class="line">方式②： <span class="keyword">SET</span> @变量名:<span class="operator">=</span>值;</span><br><span class="line">方式③： <span class="keyword">SELECT</span> @变量名:<span class="operator">=</span>值;</span><br><span class="line"><span class="comment">--2 赋值</span></span><br><span class="line">方式①： <span class="keyword">SET</span> @变量名<span class="operator">=</span>值;</span><br><span class="line">方式②： <span class="keyword">SET</span> @变量名:<span class="operator">=</span>值;</span><br><span class="line">方式③： <span class="keyword">SELECT</span> @变量名:<span class="operator">=</span>值;</span><br><span class="line">方式④： <span class="keyword">SELECT</span> 字段 <span class="keyword">INTO</span> @变量名 <span class="keyword">FROM</span> 表;(需为单值)</span><br><span class="line"><span class="comment">--3 查看变量</span></span><br><span class="line"><span class="keyword">SELECT</span> @变量名;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 局部变量</span></span><br><span class="line"><span class="comment">--1 声明并初始化</span></span><br><span class="line"><span class="keyword">DECLARE</span> 变量名 类型 <span class="keyword">DEFAULT</span> 值</span><br><span class="line"><span class="comment">--2 赋值</span></span><br><span class="line">方式①： <span class="keyword">SET</span> 变量名<span class="operator">=</span>值;</span><br><span class="line">方式②： <span class="keyword">SET</span> 变量名:<span class="operator">=</span>值;</span><br><span class="line">方式③： <span class="keyword">SELECT</span> @变量名:<span class="operator">=</span>值;</span><br><span class="line">方式④： <span class="keyword">SELECT</span> 字段 <span class="keyword">INTO</span> 变量名 <span class="keyword">FROM</span> 表;</span><br><span class="line"><span class="comment">--3 使用</span></span><br><span class="line"><span class="keyword">SELECT</span> 变量名;</span><br></pre></td></tr></table></figure>

<h3 id="2、存储过程"><a href="#2、存储过程" class="headerlink" title="2、存储过程"></a>2、存储过程</h3><p>存储过程是一组预先编译好的SQL语句的集合，类似批处理语句。对于调用者来说，存储过程封装了 SQL 语句，调用者无需考虑逻辑功能的具体实现过程。使用存储过程提高了SQL语句的重用性，<br>同时有利于降低网络负载。</p>
<h4 id="2-1-创建与调用"><a href="#2-1-创建与调用" class="headerlink" title="2.1 创建与调用"></a>2.1 创建与调用</h4><p>创建语法：CREATE PROCEDURE 存储过程名(参数列表)  BEGIN  存储过程体(一组合法的SQL语句)  END;</p>
<p>调用语法：CALL 存储过程名 结束标志</p>
<p>①如果存储过程仅为一句SQL语句BEGIN  END可省略</p>
<p>②存储过程体的SQL语句要求使用;结尾，因此需要使用 <strong>DELIMITER 结束标志</strong>  重新设置存储过程的结束标志，例如：delimiter $ 将结束标志设置为$</p>
<p>③参数列表包括三部分: 参数模式+参数名+参数类型，其中参数模式有三种： IN OUT INOUT，其中IN代表该参数作为输入，OUT代表该参数作为输出，INOUT代表该参数即可作为输入也可作为输出</p>
<p>④如果使用OUT/INOUT模式，需要先定义一个变量用于接收得到的参数值</p>
<p><em>以下示例均在MySQL命令行客户端中进行</em></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 创建参数列表为空的存储过程：用于查询stuinfo表的name、sex、phone字段数据</span></span><br><span class="line">DELIMITER $</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> myp1()</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">	<span class="keyword">SELECT</span> `name`,`sex`,`phone` <span class="keyword">FROM</span> stuinfo;</span><br><span class="line"><span class="keyword">END</span> $</span><br><span class="line"><span class="comment">--2 调用</span></span><br><span class="line"><span class="keyword">CALL</span> myp1()$</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 创建带IN模式参数的存储过程：用于根据传入的姓名查询对应的学生信息</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> myp2(<span class="keyword">IN</span> uname <span class="type">VARCHAR</span>(<span class="number">50</span>))</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">	<span class="keyword">SELECT</span> <span class="operator">*</span> </span><br><span class="line">	<span class="keyword">FROM</span> stuinfo</span><br><span class="line">	<span class="keyword">WHERE</span> stuinfo.`name`<span class="operator">=</span>uname;</span><br><span class="line"><span class="keyword">END</span> $</span><br><span class="line"><span class="comment">--2 调用</span></span><br><span class="line"><span class="keyword">CALL</span> myp2(&quot;张三&quot;) $</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 创建带OUT模式参数的存储过程：用于根据传入的姓名查询对应学生的phone并作为参数值返回</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> myp3(<span class="keyword">IN</span> uname <span class="type">VARCHAR</span>(<span class="number">50</span>),<span class="keyword">OUT</span> uphone <span class="type">VARCHAR</span>(<span class="number">11</span>))</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">	<span class="keyword">SELECT</span> phone <span class="keyword">INTO</span> uphone</span><br><span class="line">	<span class="keyword">FROM</span> stuinfo</span><br><span class="line">	<span class="keyword">WHERE</span> stuinfo.`name`<span class="operator">=</span>uname;</span><br><span class="line"><span class="keyword">END</span> $</span><br><span class="line"><span class="comment">--2 创建变量，调用存储过程，查看变量值</span></span><br><span class="line"><span class="keyword">SET</span> <span class="variable">@phone</span>_out<span class="operator">=</span><span class="string">&#x27;&#x27;</span> $</span><br><span class="line"><span class="keyword">CALL</span> myp3(&quot;张三&quot;,<span class="variable">@phone</span>_out) $</span><br><span class="line"><span class="keyword">SELECT</span> <span class="variable">@phone</span>_out $</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 创建带INOUT模式的存储过程：传入num1，num2两个值，返回各自的平方</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> myp4(<span class="keyword">INOUT</span> num1 <span class="type">INT</span> ,<span class="keyword">INOUT</span> num2 <span class="type">INT</span>)</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">	<span class="keyword">SET</span> num1<span class="operator">=</span>num1<span class="operator">*</span>num1;</span><br><span class="line">	<span class="keyword">SET</span> num2<span class="operator">=</span>num2<span class="operator">*</span>num2;</span><br><span class="line"><span class="keyword">END</span> $</span><br><span class="line"><span class="comment">--2 创建变量，调用存储过程，查看变量值</span></span><br><span class="line"><span class="keyword">set</span> <span class="variable">@num1</span><span class="operator">=</span><span class="number">10</span> $</span><br><span class="line"><span class="keyword">SET</span> <span class="variable">@num2</span><span class="operator">=</span><span class="number">6</span> $</span><br><span class="line"><span class="keyword">call</span> myp4(<span class="variable">@num1</span>,<span class="variable">@num2</span>) $</span><br><span class="line"><span class="keyword">select</span> <span class="variable">@num1</span>,<span class="variable">@num2</span> $</span><br></pre></td></tr></table></figure>

<h4 id="2-2-删除存储过程"><a href="#2-2-删除存储过程" class="headerlink" title="2.2 删除存储过程"></a>2.2 删除存储过程</h4><p>语法：DROP PROCEDURE 存储过程名 </p>
<h4 id="2-3-查看存储过程"><a href="#2-3-查看存储过程" class="headerlink" title="2.3 查看存储过程"></a>2.3 查看存储过程</h4><p>语法：SHOW CREATE PROCEDURE 存储过程名</p>
<h3 id="3、函数"><a href="#3、函数" class="headerlink" title="3、函数"></a>3、函数</h3><p>函数的优点、用途等与存储过程相同，不同的是存储过程可以无返回，也可以有多个返回，而函数只允许有一个返回。</p>
<h4 id="3-1-创建与调用"><a href="#3-1-创建与调用" class="headerlink" title="3.1 创建与调用"></a>3.1 创建与调用</h4><p>创建语法：CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型  BEGIN  函数体  END</p>
<p>调用语法：SELECT 函数名(参数) 结束标志</p>
<p>①参数列表包括参数名和参数类型</p>
<p>②函数体中必须有RETURN语句，RETURN语句可以不放在末尾</p>
<p>③如果函数体仅有一句，可以省略begin end</p>
<p>④同样需要使用DELIMITER设置结束标志</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 创建无入参函数：用于返回stuinfo中学生数</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">FUNCTION</span> myf1() <span class="keyword">RETURNS</span> <span class="type">INT</span></span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">	<span class="keyword">DECLARE</span> countx <span class="type">INT</span> <span class="keyword">DEFAULT</span> <span class="number">0</span>;</span><br><span class="line">	<span class="keyword">SELECT</span> <span class="built_in">COUNT</span>(<span class="operator">*</span>) <span class="keyword">INTO</span> countx</span><br><span class="line">	<span class="keyword">FROM</span> stuinfo;</span><br><span class="line">	<span class="keyword">RETURN</span> countx;</span><br><span class="line"><span class="keyword">END</span> $</span><br><span class="line"><span class="comment">--2 调用</span></span><br><span class="line"><span class="keyword">SELECT</span> myf1()$</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--1 创建有入参函数：用于根据传入的员工姓名返回对应的电话号码</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">FUNCTION</span> myf2(uname <span class="type">VARCHAR</span>(<span class="number">50</span>)) <span class="keyword">RETURNS</span> <span class="type">VARCHAR</span>(<span class="number">11</span>)</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">	<span class="keyword">DECLARE</span> uphone <span class="type">VARCHAR</span>(<span class="number">11</span>) <span class="keyword">DEFAULT</span> <span class="string">&#x27;&#x27;</span>;</span><br><span class="line">	<span class="keyword">SELECT</span> phone <span class="keyword">INTO</span> uphone </span><br><span class="line">	<span class="keyword">FROM</span> stuinfo</span><br><span class="line">	<span class="keyword">WHERE</span> `name`<span class="operator">=</span>uname;</span><br><span class="line">	<span class="keyword">RETURN</span> uphone; </span><br><span class="line"><span class="keyword">END</span> $</span><br><span class="line"><span class="comment">--2 调用</span></span><br><span class="line"><span class="keyword">SELECT</span> myf2(<span class="string">&#x27;张三&#x27;</span>) $</span><br></pre></td></tr></table></figure>

<h4 id="3-2-删除函数"><a href="#3-2-删除函数" class="headerlink" title="3.2 删除函数"></a>3.2 删除函数</h4><p>语法：DROP FUNCTION 函数名</p>
<h4 id="3-3-查看函数"><a href="#3-3-查看函数" class="headerlink" title="3.3 查看函数"></a>3.3 查看函数</h4><p>语法：SHOW CREATE FUNCTION 函数名</p>
<h2 id="十一、流程控制"><a href="#十一、流程控制" class="headerlink" title="十一、流程控制"></a>十一、流程控制</h2><h3 id="1、分支"><a href="#1、分支" class="headerlink" title="1、分支"></a>1、分支</h3><h4 id="1-1-IF函数"><a href="#1-1-IF函数" class="headerlink" title="1.1 IF函数"></a>1.1 IF函数</h4><p>语法：IF(条件，值1，值2)</p>
<p>①实现简单的双分支，可以应用在任何地方</p>
<p>②如果条件成立则返回值1，否则返回值2</p>
<h4 id="1-2-IF结构"><a href="#1-2-IF结构" class="headerlink" title="1.2 IF结构"></a>1.2 IF结构</h4><p>语法：IF 条件1 THEN 语句1; ELSEIF 条件2 THEN 语句2; … 【ELSE 语句n;】 END IF;</p>
<p>①实现多重分支，需要在BEGIN END中使用</p>
<h4 id="1-3-CASE函数"><a href="#1-3-CASE函数" class="headerlink" title="1.3 CASE函数"></a>1.3 CASE函数</h4><p>情况一：用于实现等值判断<br>语法:<br>    CASE 变量|表达式|字段<br>    WHEN 要判断的值 THEN 返回的值1或语句1；<br>    WHEN 要判断的值 THEN 返回的值2或语句2；<br>    …<br>    ELSE 要返回的值n或语句n；<br>    END CASE;</p>
<p>情况二：用于实现区间判断<br>语法：<br>    CASE<br>    WHEN 要判断的条件1 THEN 返回的值1或语句1;<br>    WHEN 要判断的条件2 THEN 返回的值2或语句2;<br>    …<br>    ELSE 要返回的值n或语句n；<br>    END CASE；</p>
<p>①作为表达式，嵌套在其他的语句中使用，可以放在任何地方</p>
<p>②作为独立语句，放在BEGIN END中</p>
<p>③如果WHEN中的值满足或条件成立，则执行对应的THEN后的语句，并且结束CASE，如果都不满足则执行ELSE中的语句或值</p>
<p>④ELSE可以省略，如果ELSE省略了，并且所有WHEN条件都不满足，则返回NULL</p>
<h3 id="2、循环"><a href="#2、循环" class="headerlink" title="2、循环"></a>2、循环</h3><p>循环控制包括两部分：ITERATE指示循环继续，类似于continue；LEAVE指示跳出循环，类似于break</p>
<h4 id="2-1-WHILE循环"><a href="#2-1-WHILE循环" class="headerlink" title="2.1 WHILE循环"></a>2.1 WHILE循环</h4><p>语法：【标签：】 WHILE 循环条件 DO  循环体； END WHILE 【标签】;</p>
<h4 id="2-2-LOOP循环"><a href="#2-2-LOOP循环" class="headerlink" title="2.2 LOOP循环"></a>2.2 LOOP循环</h4><p>主要用于简单的死循环</p>
<p>语法：【标签：】 LOOP 循环体 END LOOP 【标签】;</p>
<h4 id="2-3-REPEAT循环"><a href="#2-3-REPEAT循环" class="headerlink" title="2.3 REPEAT循环"></a>2.3 REPEAT循环</h4><p>语法：【标签：】 REPEAT 循环体; UNTIL 结束循环的条件; END REPEAT 【标签】；</p>
<hr>
<p><em>参考资料：</em></p>
<p><em>[1] 《SQL必知必会》</em></p>
<p><em>[2] 《<a target="_blank" rel="noopener" href="http://c.biancheng.net/mysql/">MySQL教程：MySQL数据库学习宝典（从入门到精通） (biancheng.net)</a>》</em></p>
<p><em>[3] 《<a target="_blank" rel="noopener" href="https://blog.csdn.net/belen_xue/article/details/54357018">mysql字段类型tinyint、smallint、mediumint、int、bigint详解_贝伦酱-CSDN博客</a>》</em></p>
<p><em>[4] 《<a target="_blank" rel="noopener" href="https://blog.csdn.net/oschina_41790905/article/details/80004496">SQL语句之查询数据（MySQL）Star’s Tech Blog-CSDN博客sql查询数据</a>》</em></p>
<p><em>[5] 《<a target="_blank" rel="noopener" href="https://www.bilibili.com/video/BV1xW411u7ax">尚硅谷MySQL数据库全套完整版（sql数据库优化）_哔哩哔哩 (゜-゜)つロ 干杯~-bilibili</a>》</em></p>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="mailto:undefined">h0ss</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="https://blog.gpnusz.cn/2021/05/07/MySQL%E7%9F%A5%E8%AF%86%E6%95%B4%E5%90%88/">https://blog.gpnusz.cn/2021/05/07/MySQL%E7%9F%A5%E8%AF%86%E6%95%B4%E5%90%88/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="https://blog.gpnusz.cn" target="_blank">后端学习记录</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/SQL/">SQL</a></div><div class="post_share"></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/2021/06/04/%E6%AD%A3%E5%88%99%E8%A1%A8%E8%BE%BE%E5%BC%8F-python/"><img class="prev-cover" src="https://i.loli.net/2021/09/01/4oSNuD31CVRgPQz.jpg" onerror="onerror=null;src='/img/404.jpg'" alt="cover of previous post"><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">正则表达式-python</div></div></a></div><div class="next-post pull-right"><a href="/2021/03/14/Java%E9%9B%86%E5%90%88%E6%A1%86%E6%9E%B6/"><img class="next-cover" src="https://i.loli.net/2021/09/01/4ZYBfbgnAz283rL.jpg" onerror="onerror=null;src='/img/404.jpg'" alt="cover of next post"><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">Java集合框架</div></div></a></div></nav></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="card-info-avatar is-center"><img class="avatar-img" src="/img/avatar.jpg" onerror="this.onerror=null;this.src='/img/friend_404.gif'" alt="avatar"/><div class="author-info__name">h0ss</div><div class="author-info__description">一个苦逼后端仔的学习记录</div></div><div class="card-info-data"><div class="card-info-data-item is-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">51</div></a></div><div class="card-info-data-item is-center"><a href="/tags/"><div class="headline">标签</div><div class="length-num">14</div></a></div></div><a class="button--animated" id="card-info-btn" target="_blank" rel="noopener" href="https://gitee.com/h0ss"><i class="fab fa-github"></i><span>Gitee仓库</span></a></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn card-announcement-animation"></i><span>公告</span></div><div class="announcement_content">Java后端技术栈语雀文档：https://www.yuque.com/oliver-wdtb1/java</div></div><div class="sticky_layout"><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2022/11/16/%E7%BB%99%E5%BC%80%E6%BA%90%E9%A1%B9%E7%9B%AE%E6%8F%90%E4%BA%86%E4%B8%AAPR/" title="给开源项目提了个PR">给开源项目提了个PR</a><time datetime="2022-11-15T17:28:40.000Z" title="发表于 2022-11-16 01:28:40">2022-11-16</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2022/09/03/%E3%80%90MySQL%E3%80%91InnoDB%E7%9A%84%E8%A1%8C%E6%A0%BC%E5%BC%8F/" title="【MySQL】InnoDB的行格式">【MySQL】InnoDB的行格式</a><time datetime="2022-09-02T18:17:38.000Z" title="发表于 2022-09-03 02:17:38">2022-09-03</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2022/08/21/%E3%80%90Debug%E3%80%91bitField-%E5%BC%95%E5%8F%91%E7%9A%84%E6%A0%88%E6%BA%A2%E5%87%BA%E6%8E%92%E9%94%99%E8%AE%B0/" title="【Debug】bitField 引发的栈溢出排错记">【Debug】bitField 引发的栈溢出排错记</a><time datetime="2022-08-20T18:10:40.000Z" title="发表于 2022-08-21 02:10:40">2022-08-21</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2022/07/16/%E3%80%90RPC%E3%80%91%E8%AF%A6%E8%A7%A3SPI%E6%9C%BA%E5%88%B6/" title="【RPC】详解SPI机制">【RPC】详解SPI机制</a><time datetime="2022-07-15T18:51:57.000Z" title="发表于 2022-07-16 02:51:57">2022-07-16</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2022/07/14/%E3%80%90Redis%E3%80%91%E9%87%8D%E8%A6%81%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84%E5%9F%BA%E7%A1%80/" title="【Redis】重要数据结构基础">【Redis】重要数据结构基础</a><time datetime="2022-07-13T17:22:08.000Z" title="发表于 2022-07-14 01:22:08">2022-07-14</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div><script src="/js/utils.js"></script><script src="/js/main.js"></script><div class="js-pjax"></div><script defer="defer" id="fluttering_ribbon" mobile="false" src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1/dist/canvas-fluttering-ribbon.min.js"></script><script id="canvas_nest" defer="defer" color="0,0,255" opacity="0.7" zIndex="-1" count="99" mobile="false" src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1/dist/canvas-nest.min.js"></script><script id="click-heart" src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1/dist/click-heart.min.js" async="async" mobile="false"></script><script id="click-show-text" src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1/dist/click-show-text.min.js" data-mobile="false" data-text="爱国,敬业,诚信,友善,自由,平等,公正,法治,富强,民主,文明,和谐" data-fontsize="16px" data-random="false" async="async"></script></div></body></html>